package br.unicamp.ic.lis.docseaservice.model.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.log4j.Logger;

import br.unicamp.ic.lis.docseaservice.exception.DBException;

/**
 * 
 * @author joaosavio
 */
public class QueryHandler {

	// private static final String FAILED_TO_ROLLBACK = "Failed to rollback";
	private static final String RESULT = "Result: ";
	private static final String EXECUTING_QUERY = "Executing query: ";
	private static final String FAILED_TO_CLOSE_CONNECTION = "Failed to close connection";
	private static final String FAILED_TO_EXECUTE_QUERY = "Failed to execute query: ";
	private static Logger LOG = Logger.getLogger(QueryHandler.class);

	public QueryHandler() {

	}

	/**
	 * 
	 * @param <T>
	 * @param sql
	 * @param resultSetHandler
	 * @param params
	 * @return
	 */
	public <T> T execute(String sql, ResultSetHandler<T> resultSetHandler,
			Object... params) {
		Connection connection = null;
		try {
			connection = ConnectionHandler.getConnection();
			QueryRunner queryRunner = new QueryRunner();

			if (LOG.isDebugEnabled()) {
				if (params != null && params.length != 0) {
					PreparedStatement pstmt = connection.prepareStatement(sql);
					queryRunner.fillStatement(pstmt, params);
					LOG.debug(EXECUTING_QUERY + pstmt);
					pstmt.close();
				} else {
					LOG.debug(EXECUTING_QUERY + sql);
				}
			}

			T t = queryRunner.query(connection, sql, resultSetHandler, params);
			LOG.debug(RESULT + t);
			return t;
		} catch (SQLException e) {
			LOG.error(FAILED_TO_EXECUTE_QUERY + sql, e);
			throw new DBException(FAILED_TO_EXECUTE_QUERY + sql, e);
		} finally {
			close(connection);
		}
	}

	/**
	 * 
	 * @param sql
	 * @param params
	 */
	public void execute(String sql, Object... params) {
		Connection connection = null;
		PreparedStatement pstmt = null;
		try {
			connection = ConnectionHandler.getConnection();
			QueryRunner queryRunner = new QueryRunner();

			if (params != null) {
				pstmt = connection.prepareStatement(sql);
				queryRunner.fillStatement(pstmt, params);
				LOG.debug(EXECUTING_QUERY + pstmt);

			} else {
				LOG.debug(EXECUTING_QUERY + sql);
			}

			queryRunner.update(connection, sql, params);
		} catch (SQLException e) {
			/*
			 * try { DbUtils.rollback(connection); } catch (SQLException e1) {
			 * LOG.error(FAILED_TO_ROLLBACK, e1); throw new
			 * DBException(FAILED_TO_ROLLBACK, e1); }
			 */
			LOG.error(FAILED_TO_EXECUTE_QUERY + sql, e);
			throw new DBException(FAILED_TO_EXECUTE_QUERY + sql, e);
		} finally {
			close(connection, pstmt);
		}
	}

	/**
	 * 
	 * @param connection
	 * @param pstmt
	 */
	private void close(Connection connection, PreparedStatement pstmt) {
		if (connection != null) {
			try {
				if (pstmt != null) {
					DbUtils.close(pstmt);
				}
				DbUtils.close(connection);
			} catch (SQLException e) {
				LOG.error(FAILED_TO_CLOSE_CONNECTION, e);
				throw new DBException(FAILED_TO_CLOSE_CONNECTION, e);
			}
		}
	}

	private void close(Connection connection) {
		close(connection, null);
	}
}
